﻿CREATE proc [dbo].[sp_getDailyPayData](@StartDate as Datetime)
as
/*
统计自开始日期到现在，每天支付数据
*/
begin 

select a.[date] ,a.apptCnt ,a.appFee,b.regCnt,b.regFee,(a.appFee + b.regFee) as sumRegFee,c.payCnt,c.payFee,(a.appFee + b.regFee + c.payFee) as sumFee,
c.yiBaoCnt,c.yiBaoMoney,c.yibaoPersonMoney,c.yiBaoPrescMoney,(a.appFee+b.regFee+c.payFee-c.yiBaoMoney) as pureSumFee from 
(select CONVERT(varchar,createtime,23) as [Date],COUNT(*) as apptCnt ,sum(SumFee) as appFee from ApptOrder 
where PayState = 1 and CreateTime >@StartDate and CONVERT(varchar,createtime,23) <> RegDate 
group by CONVERT(varchar,createtime,23)) a ,
(select CONVERT(varchar,createtime,23) as [Date],COUNT(*) as regCnt ,sum(SumFee) as regFee from ApptOrder 
where PayState = 1 and CreateTime >@StartDate and CONVERT(varchar,createtime,23) = RegDate 
group by CONVERT(varchar,createtime,23)) b ,
(select CONVERT(varchar,createtime,23) as [Date],COUNT(*) as payCnt,sum(PrescMoney) as payFee,
sum (case when YiBaoJydjh is null then  0 else 1 end) as YiBaoCnt,
SUM(isnull(YibaoMoney,0))as YiBaoMoney ,SUM(isnull(PersonalMoney,0)) as YibaoPersonMoney,
SUM(isnull(PersonalMoney,0))+SUM(isnull(YibaoMoney,0)) as YiBaoPrescMoney from BillOrder 
where PayState = 1 and OrderStatus = 8 and  CreateTime >@StartDate
group by CONVERT(varchar,createtime,23)) c 
where a.[Date] = b.[Date] and a.[Date]= c.[Date]
order by a.Date 

end
